﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;//用来连接数据库的
using System.Data;//这是用来使用这个的： //关闭连接对象
//return comm.ExecuteReader(CommandBehavior.CloseConnection);  

using System.Configuration;
using System.Reflection;  

namespace SystemDAL
{
    /// <summary>
    /// 数据库操作类 
    /// </summary>
    public class DbContext
    {
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        string connStr = ConfigurationManager.ConnectionStrings["SQL"].ConnectionString;

        #region ExecuteNonQuery
        /// <summary>
        /// 执行新增、删除、修改
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public bool ExecNonQuery(string sql)
        {
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand(sql, conn);
                return comm.ExecuteNonQuery() > 0;
            }
            catch (Exception ex)
            {
                MessageBox.Show("异常!错误信息是：" + ex.Message + "请联系工作人员排除问题！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Error);
                System.Environment.Exit(0);
                return false;
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion
        
        #region ExecuteScalar
        /// <summary>
        /// 执行查询，返回单个值
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public object ExecScaler(string sql)
        {
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand(sql, conn);
                return comm.ExecuteScalar();
            }
            catch (Exception ex)
            {
                MessageBox.Show("异常!错误信息是：" + ex.Message + "请联系工作人员排除问题！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Error);
                System.Environment.Exit(0);
                return null;
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion

        #region ExecuteReader
        /// <summary>
        /// 执行多个查询
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>返回数据读取器</returns>
        public SqlDataReader ExecReader(string sql)
        {
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand(sql, conn);
                //CommandBehavior.CloseConnection表示关闭读取器的时候，自动
                //关闭连接对象
                return comm.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                MessageBox.Show("异常!错误信息是：" + ex.Message + "请联系工作人员排除问题！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Error);
                System.Environment.Exit(0);
                return null;
            }
        }
        #endregion

        #region 数据适配器
        /// <summary>
        /// 数据适配器
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataSet ExecDataSet(string sql)
        {
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                //数据适配器
                var adp = new SqlDataAdapter(sql, conn);
                //数据集
                DataSet ds = new DataSet();
                //填充数据集
                adp.Fill(ds);
                return ds;
            }
            catch(Exception ex)
            {
                MessageBox.Show("异常!错误信息是：" + ex.Message+"请联系工作人员排除问题！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Error);
                System.Environment.Exit(0);
                return null;
            }
        }
        #endregion

        /*#region DataTable转换list泛型（反射）
        /// <summary>
        /// DataTable转换list泛型（反射）
        /// </summary>
        /// <typeparam name="T">目标实体</typeparam>
        /// <param name="dt">源数据表</param>
        /// <returns>List类型的泛型集合</returns>
        public static List<T> ToList<T>(this DataTable dt)
        {
            var list = new List<T>();
            Type t = typeof(T);
            var plist = new List<PropertyInfo>(typeof(T).GetProperties());
            foreach (DataRow item in dt.Rows)
            {
                T s = Activator.CreateInstance<T>();
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    PropertyInfo info = plist.Find(p => p.Name == dt.Columns[i].ColumnName);
                    if (info != null)
                    {
                        if (!Convert.IsDBNull(item[i]))
                        {
                            info.SetValue(s, item[i], null);
                        }
                    }
                }
                list.Add(s);
            }
            return list;
        }
        #endregion*/
    }
}
